package com.auto.survey.database

import android.content.Context
import androidx.room.Database
import androidx.room.Room
import androidx.room.RoomDatabase
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase
import com.auto.survey.activity.adjustment.adjustment_high.dao.AdjustmentHighDao
import com.auto.survey.activity.adjustment.adjustment_high.entity.KnownPoint
import com.auto.survey.activity.adjustment.adjustment_high.entity.Observation
import com.auto.survey.activity.adjustment.adjustment_high.entity.Project
import com.auto.survey.activity.adjustment.adjustment_high.entity.UnknownPoint
import com.auto.survey.activity.config.dao.ConfigDao
import com.auto.survey.activity.config.entity.ConfigBean
import com.auto.survey.activity.config.entity.PointConfig
import com.auto.survey.activity.monitoring.dao.ClosedLoopObservationDao
import com.auto.survey.activity.monitoring.dao.LoopObservationPointDao
import com.auto.survey.activity.monitoring.dao.MonitorCheckPointDao
import com.auto.survey.activity.monitoring.dao.MonitoringObjectDao
import com.auto.survey.activity.monitoring.dao.MonitoringPointCtrlObservationDao
import com.auto.survey.activity.monitoring.dao.MonitoringPointDao
import com.auto.survey.activity.monitoring.dao.MonitoringPointWithCtrlObservDao
import com.auto.survey.activity.monitoring.dao.MonitoringProjectDao
import com.auto.survey.activity.monitoring.dao.MonitoringStationDao
import com.auto.survey.activity.monitoring.dao.PointDao
import com.auto.survey.activity.monitoring.dao.PointWithCheckedPointDao
import com.auto.survey.activity.monitoring.entity.MonitorCheckPoint
import com.auto.survey.activity.monitoring.entity.MonitoringObject
import com.auto.survey.activity.monitoring.entity.MonitoringObservation
import com.auto.survey.activity.monitoring.entity.MonitoringPoint
import com.auto.survey.activity.monitoring.entity.MonitoringPointCtrlObservation
import com.auto.survey.activity.monitoring.entity.MonitoringStation
import com.auto.survey.activity.monitoring.entity.MontoringProject
import com.auto.survey.activity.monitoring.entity.Point
import com.auto.survey.activity.monitoring.entity.ctrlmeasure.ClosedLoopObservation
import com.auto.survey.activity.monitoring.entity.ctrlmeasure.LoopObservationPoint
import com.auto.survey.util.monitoring.dao.CalculationResultDao
import com.auto.survey.util.monitoring.dao.MonitoringStationCalculationResultDao
import com.auto.survey.util.monitoring.dao.NonCompliantPointDao
import com.auto.survey.util.monitoring.dao.VerificationResultDao
import com.auto.survey.util.monitoring.entity.NonCompliantPoint
import com.auto.survey.util.monitoring.entity.RearIntersectionCalculationResult
import com.auto.survey.util.monitoring.entity.RearIntersectionVerificationResult

@Database(
    entities = [ConfigBean::class, PointConfig::class,
        Project::class, KnownPoint::class,
        UnknownPoint::class, Observation::class,
        MontoringProject::class, MonitoringObject::class,
        MonitoringStation::class, MonitoringPoint::class,
        MonitoringObservation::class, MonitorCheckPoint::class,
        RearIntersectionCalculationResult::class, NonCompliantPoint::class,
        RearIntersectionVerificationResult::class, Point::class,
        MonitoringPointCtrlObservation::class, ClosedLoopObservation::class,
        LoopObservationPoint::class], // 列出所有实体类
    version = 27, // 数据库版本，升级时需增加
    exportSchema = true // 不导出模式历史（可选）
)
abstract class AppDatabase :RoomDatabase(){

    abstract  fun configDao():ConfigDao

    abstract  fun adjustmentHighDao(): AdjustmentHighDao

    abstract fun monitoringProjectDao(): MonitoringProjectDao

    abstract fun monitoringObjectDao(): MonitoringObjectDao

    abstract fun monitoringStationDao(): MonitoringStationDao

    abstract fun monitoringPointDao(): MonitoringPointDao

    abstract fun monitoringCheckPointDao(): MonitorCheckPointDao

    abstract fun monitoringWithCheckPointDao():PointWithCheckedPointDao

    abstract fun calculationStationDao(): MonitoringStationCalculationResultDao
    abstract fun nonCompliantPointDao(): NonCompliantPointDao
    abstract fun verificationResultDao(): VerificationResultDao
    abstract fun calculationDao(): CalculationResultDao

    abstract fun pointDao(): PointDao

    abstract fun moitoringPointCtrlObservDao(): MonitoringPointCtrlObservationDao

    abstract fun monitoringPointWithCtrlObservDao(): MonitoringPointWithCtrlObservDao

    abstract fun closedLoopObservationDao(): ClosedLoopObservationDao

    abstract fun loopObservationPointDao(): LoopObservationPointDao

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

        // 获取单例数据库实例
        fun getInstance(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                INSTANCE ?: Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "survey_config_database"
                )
                    .addMigrations(MIGRATION_12_13)
                    .fallbackToDestructiveMigration()
                    .build()
                    .also { INSTANCE = it }
            }
        }

        val MIGRATION_12_13: Migration = object : Migration(12, 13) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 修改 ClosedLoopObservation 表，将 startPointId 和 endPointId 改为可空
                database.execSQL("ALTER TABLE MonitoringPoint " +
                        "ALTER COLUMN pointHz REAL") // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPoint " +
                        "ALTER COLUMN pointV REAL")   // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPoint " +
                        "ALTER COLUMN pointSD REAL")   // 移除 NOT NULL 约束
            }
        }


        // 新增：版本21→22的迁移脚本（处理stationId字段）
        val MIGRATION_11_12: Migration = object : Migration(11, 12) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 修改 ClosedLoopObservation 表，将 startPointId 和 endPointId 改为可空
                database.execSQL("ALTER TABLE MonitoringPointCtrlObservation " +
                        "ALTER COLUMN observationTime TEXT") // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPointCtrlObservation " +
                        "ALTER COLUMN horizontalAngleL REAL")   // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPointCtrlObservation " +
                        "ALTER COLUMN verticalAngleL REAL")   // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPointCtrlObservation " +
                        "ALTER COLUMN distanceL REAL")   // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPointCtrlObservation " +
                        "ALTER COLUMN horizontalAngleR REAL")   // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPointCtrlObservation " +
                        "ALTER COLUMN verticalAngleR REAL")   // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPointCtrlObservation " +
                        "ALTER COLUMN distanceR REAL")   // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPointCtrlObservation " +
                        "ALTER COLUMN measure2C REAL")   // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE MonitoringPointCtrlObservation " +
                        "ALTER COLUMN measure2D REAL")   // 移除 NOT NULL 约束
            }
        }

        // 新增：版本21→22的迁移脚本（处理stationId字段）
        val MIGRATION_10_11: Migration = object : Migration(10, 11) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 修改 ClosedLoopObservation 表，将 startPointId 和 endPointId 改为可空
                database.execSQL("ALTER TABLE ClosedLoopObservation " +
                        "ALTER COLUMN startPointId INTEGER") // 移除 NOT NULL 约束

                database.execSQL("ALTER TABLE ClosedLoopObservation " +
                        "ALTER COLUMN endPointId INTEGER")   // 移除 NOT NULL 约束
            }
        }

        // 新增：版本21→22的迁移脚本（处理stationId字段）
        val MIGRATION_9_10: Migration = object : Migration(9, 10) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 为MonitoringPoint表添加stationId字段
                // 注意：新增非空字段必须指定默认值（根据业务场景调整默认值，这里用0示例）
                database.execSQL("""
                    ALTER TABLE MonitoringPoint 
                    ADD COLUMN stationId INTEGER NOT NULL DEFAULT 0
                """.trimIndent())
            }
        }

        val MIGRATION_8_9: Migration = object : Migration(8, 9) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 1. 创建新表结构（不含stationId字段）
                database.execSQL("""
            CREATE TABLE MonitoringObservation_new (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                pointId INTEGER NOT NULL,
                observationTime TEXT NOT NULL,
                horizontalAngle REAL NOT NULL,
                verticalAngle REAL NOT NULL,
                distance REAL NOT NULL,
                temperature REAL NOT NULL,
                pressure REAL NOT NULL,
                humidity REAL NOT NULL,
                correctedX REAL NOT NULL,
                correctedY REAL NOT NULL,
                correctedZ REAL NOT NULL,
                dx REAL NOT NULL,
                dy REAL NOT NULL,
                dz REAL NOT NULL,
                totalDisplacement REAL NOT NULL,
                createTime TEXT NOT NULL,
                updateTime TEXT NOT NULL,
                adjustmentId INTEGER,
                FOREIGN KEY(pointId) REFERENCES MonitoringPoint(id) ON DELETE CASCADE
            )
        """)

                // 2. 复制数据（忽略stationId字段）
                database.execSQL("""
            INSERT INTO MonitoringObservation_new (
                id, pointId, observationTime, horizontalAngle, verticalAngle,
                distance, temperature, pressure, humidity, correctedX,
                correctedY, correctedZ, dx, dy, dz, totalDisplacement,
                createTime, updateTime, adjustmentId
            )
            SELECT 
                id, pointId, observationTime, horizontalAngle, verticalAngle,
                distance, temperature, pressure, humidity, correctedX,
                correctedY, correctedZ, dx, dy, dz, totalDisplacement,
                createTime, updateTime, adjustmentId
            FROM MonitoringObservation
        """)

                // 3. 删除旧表
                database.execSQL("DROP TABLE MonitoringObservation")

                // 4. 重命名新表
                database.execSQL("ALTER TABLE MonitoringObservation_new RENAME TO MonitoringObservation")

                // 5. 重新创建索引
                database.execSQL("CREATE INDEX index_MonitoringObservation_pointId ON MonitoringObservation(pointId)")
            }
        }

        val MIGRATION_7_8: Migration = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 为 point_config_table 表添加 position 列
                database.execSQL(
                    """
            ALTER TABLE MonitoringCheckPoint 
            ADD COLUMN isNew BOOLEAN NOT NULL DEFAULT FALSE
        """.trimIndent()
                )
            }
        }


        val MIGRATION_6_7: Migration = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 为 point_config_table 表添加 position 列
                database.execSQL(
                    """
            ALTER TABLE MonitoringPoint 
            ADD COLUMN collected BOOLEAN NOT NULL DEFAULT FALSE
        """.trimIndent()
                )
            }
        }


        val MIGRATION_5_6: Migration = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 为 point_config_table 表添加 position 列
                database.execSQL(
                    """
            ALTER TABLE MonitoringPoint 
            ADD COLUMN mirrorType INT NOT NULL DEFAULT 0
        """.trimIndent()
                )
            }
        }


        val MIGRATION_4_5: Migration = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 为 point_config_table 表添加 position 列
                database.execSQL(
                    """
            ALTER TABLE MonitoringPoint 
            ADD COLUMN mirrorHeight DOUBLE NOT NULL DEFAULT 0
        """.trimIndent()
                )
            }
        }

        // 在 AppDatabase 类中定义迁移
        val MIGRATION_3_4: Migration = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 为 point_config_table 表添加 position 列
                database.execSQL(
                    """
            DROP TABLE ATS_AdjustmentResult 
            DROP TABLE ATS_DeformationAnalysis 
            DROP TABLE ATS_DeformationAnalysis 
            DROP TABLE ATS_MonitoringObject 
            DROP TABLE ATS_Observation 
            DROP TABLE ATS_POINT 
            DROP TABLE ATS_Project 
            DROP TABLE ATS_QualityControl 
            DROP TABLE ATS_STATION 
            DROP TABLE ATS_WarningThreshold 
        """.trimIndent()
                )
            }
        }

        val MIGRATION_2_3: Migration = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 为 point_config_table 表添加 position 列
                database.execSQL(
                    """
            ALTER TABLE point_config_table 
            ADD COLUMN position INTEGER NOT NULL DEFAULT 0
        """.trimIndent()
                )
            }
        }

        // 在 AppDatabase 类中定义迁移
        val MIGRATION_1_2: Migration = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 为 point_config_table 表添加 position 列
                database.execSQL(
                    """
            ALTER TABLE point_config_table 
            ADD COLUMN position INTEGER DEFAULT 0
        """.trimIndent()
                )
            }
        }
    }
}